CentOS 7
Sponsored Link

PostgreSQL 9.2 : Replication Settings
2015/07/26
 
Configure PostgreSQL Replication settings. This configuration is Master-Slave settings.
[1] Configure Master Server.
[root@dlp ~]#
yum -y install postgresql-server
[root@dlp ~]#
postgresql-setup initdb

Initializing database ... OK
[root@dlp ~]#
vi /var/lib/pgsql/data/postgresql.conf
# line 59: uncomment and change

listen_addresses = '
*
'
# line 165: uncomment and change

wal_level =
hot_standby
# line 168: uncomment and change

# on ⇒ sync

# remote_write ⇒ memory sync

# local ⇒ slave is asynchronous

# off ⇒ asynchronous

synchronous_commit =
local
# line 194: uncomment and change (enable archive_mode)

archive_mode =
on
# line 196: uncomment and change (command to get archives)

archive_command = '
cp %p /var/lib/pgsql/archive/%f
'
# line 212: uncomment and change (slave servers + 1)

max_wal_senders =
2
# line 214: uncomment and change

wal_keep_segments =
10
# line 221: uncomment and change (any name you like)

synchronous_standby_names = '
slave01
'
[root@dlp ~]#
vi /var/lib/pgsql/data/pg_hba.conf
# add to the end

# host replication [replication user] [allowed IP addresses] password

host    replication     replica          127.0.0.1/32            md5
host    replication     replica          10.0.0.30/32            md5
host    replication     replica          10.0.0.51/32            md5
[root@dlp ~]#
systemctl start postgresql

[root@dlp ~]#
systemctl enable postgresql

# create a user for replication

[root@dlp ~]#
su - postgres
-bash-4.2$
createuser --replication -P replica

Enter password for new role:
Enter it again:
[2] If Firewalld is running, allow PostgreSQL service.
[root@dlp ~]#
firewall-cmd --add-service=postgresql --permanent

success
[root@dlp ~]#
firewall-cmd --reload

success
[3] Configure Slave Server.
[root@node01 ~]#
yum -y install postgresql-server
[root@node01 ~]#
su - postgres
# get backup from Master Server

-bash-4.2$
pg_basebackup -h 10.0.0.30 -U replica -D /var/lib/pgsql/data -P --xlog

Password:    
# "replica" user's password
-bash-4.2$
vi /var/lib/pgsql/data/postgresql.conf
# line 230: uncomment and change

hot_standby =
on
-bash-4.2$
cp /usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/recovery.conf

-bash-4.2$
vi /var/lib/pgsql/data/recovery.conf
# line 44: uncomment and change (command to get archives)

restore_command = '
scp 10.0.0.30:/var/lib/pgsql/archive/%f %p
'
# line 108: uncomment and change

standby_mode =
on
# line 115: uncomment and change (connection info to Master Server)

primary_conninfo = '
host=10.0.0.30 port=5432 user=replica password=password application_name=slave01
'
-bash-4.2$
exit

logout
[root@node01 ~]#
systemctl start postgresql

[root@node01 ~]#
systemctl enable postgresql

[4] It's OK all if result of the command below is like follows. Make sure the setting works normally to create databases on Master Server.
-bash-4.2$
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;" 

 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 slave01          | streaming |             1 | sync
(1 row)
 
Tweet